﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlServerCe;

namespace BalanceManagerApp.DataAccess
{
    class SqlCeDbQueryHelper : IDisposable
    {
        SqlCeConnection connection;
        SqlCeTransaction transaction;
        bool disposed;

        public SqlCeDbQueryHelper()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            this.connection = new SqlCeConnection(connectionString);
            this.connection.Open();
            this.transaction = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
        }

        public void Dispose()
        {
            Dispose(true);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    this.transaction.Commit();
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    connection.Dispose();
                }
                this.disposed = true;
            }
        }

        public object ExecuteScalar(string query, Dictionary<string, object> parameters)
        {
            try
            {
                var command = new SqlCeCommand(query, connection);
                BuildParameters(command, parameters);
                var result = command.ExecuteScalar();
                return result;
            }
            catch
            {
                this.transaction.Rollback();
                throw;
            }
        }

        public void ExecuteNonQuery(string query, Dictionary<string, object> parameters)
        {
            try
            {
                var command = new SqlCeCommand(query, connection);
                BuildParameters(command, parameters);
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                this.transaction.Rollback();
                throw;
            }
        }

        public SqlCeDataReader ExecuteReader(string query, Dictionary<string, object> parameters)
        {
            try
            {
                var command = new SqlCeCommand(query, connection);
                BuildParameters(command, parameters);
                var reader = command.ExecuteReader();

                return reader;
            }
            catch (Exception)
            {
                this.transaction.Rollback();
                throw;
            }
           
        }

        static void BuildParameters(SqlCeCommand command, Dictionary<string, object> parameters)
        {
            if (parameters == null || parameters.Count == 0) return;
            
            foreach (var parameter in parameters)
                command.Parameters.Add(new SqlCeParameter(parameter.Key, (object)parameter.Value));
        }
    }
}
